﻿using System;
using System.Data;
using System.Data.SqlClient;
using System.Text;

using Model;

namespace DAL
{
    public  class CommentDAL
    {
        /// <summary>
        /// 获得数据列表
        /// </summary>
        public static DataSet GetAllComment()
        {
            return DbHelperSQL.Query("select * from ExamDB.dbo.tb_Comment");
        }

        public static DataSet GetCommentByDimension(string dimID)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select * ");
            strSql.Append(" FROM ExamDB.dbo.tb_Comment ");
            strSql.Append(@"where question_dimensionid = ");
            strSql.Append(dimID);

            return DbHelperSQL.Query(strSql.ToString());
        }

        public static DataSet GetCommentByPosition(string positionID)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select distinct tb_Comment.dimension_id as dimension_id ,dimension_name,posion_id ");
            strSql.Append(" FROM ExamDB.dbo.tb_Comment,ExamDB.dbo.tb_Dimension ");
            strSql.Append(@"where ExamDB.dbo.tb_Comment.dimension_id=tb_Dimension.dimension_id and posion_id = ");
            strSql.Append(positionID);

            return DbHelperSQL.Query(strSql.ToString());
        }

        public static DataSet GetCommentByAll(int positionId, int dimensionId, int reportpartId, int regionId)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select comment_nm ");
            strSql.Append(" FROM ExamDB.dbo.tb_Comment ");
            strSql.Append(@"where dimension_id = ");
            strSql.Append(dimensionId);
            strSql.Append(@"and posion_id=");
            strSql.Append(positionId);
            strSql.Append(@"and report_part_id=");
            strSql.Append(reportpartId);
            strSql.Append(@"and region_id=");
            strSql.Append(regionId);

            return DbHelperSQL.Query(strSql.ToString());
        }

        public static DataSet GetCommentByReportPart(string reportID)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select * ");
            strSql.Append(" FROM ExamDB.dbo.tb_Comment ");
            strSql.Append(@"where report_part_id = ");
            strSql.Append(reportID);

            return DbHelperSQL.Query(strSql.ToString());
        }

        /// <summary>
        /// 增加一条数据
        /// </summary>
        public static int Add( Comment model)
        {
            StringBuilder strSql = new StringBuilder();

            strSql.Append("if exists(SELECT * FROM dbo.tb_Comment WHERE posion_id=" + model.Posion_id.ToString() + " and report_part_id=" + model.Report_part_id + "and region_id=" + model.Region_id + "and dimension_id= "+model.Dimension_id  + ")");
            strSql.Append("delete from dbo.tb_Comment WHERE posion_id=" + model.Posion_id.ToString() + " and report_part_id=" + model.Report_part_id + "and region_id=" + model.Region_id + "and dimension_id= " + model.Dimension_id);

            DbHelperSQL.ExecuteSql(strSql.ToString());
            strSql.Clear();
            strSql.Append("insert into ExamDB.dbo.tb_Comment(");
            strSql.Append(@"posion_id,
                            report_part_id,
                            region_id,
                            comment_nm,
                            dimension_id)");

            strSql.Append(" values (");

            strSql.Append(@"@posion_id,
                            @report_part_id,
                            @region_id,
                            @comment_nm,
                            @dimension_id)");

            strSql.Append(";select @@IDENTITY");
            SqlParameter[] parameters = {
					new SqlParameter("@posion_id", SqlDbType.Int,4),
					new SqlParameter("@report_part_id", SqlDbType.Int,4),
					new SqlParameter("@region_id", SqlDbType.Int ,4),
					new SqlParameter("@comment_nm", SqlDbType.NVarChar,255),
                    new SqlParameter("@dimension_id",SqlDbType.Int,4)
                                        };
            parameters[0].Value = model.Posion_id;
            parameters[1].Value = model.Report_part_id ;
            parameters[2].Value = model.Region_id;
            parameters[3].Value = model.Comment_nm;
            parameters[4].Value = model.Dimension_id;


            object obj = DbHelperSQL.GetSingle(strSql.ToString(), parameters);
            if (obj == null)
            {
                return -1;
            }
            else
            {
                return Convert.ToInt32(obj);
            }
        }

        /// <summary>
        /// 更新一条数据
        /// </summary>
        public static void Update(Comment  model)
        {
            StringBuilder strSql = new StringBuilder();

            strSql.Append("update ExamDB.dbo.tb_Comment set ");
            strSql.Append("comment_nm=@comment_nm");
            strSql.Append(" where posion_id=@posion_id and report_part_id=@report_part_id and region_id=@region_id and dimension_id=@dimension_id ");
            
            SqlParameter[] parameters = {
                    new SqlParameter("@comment_nm", SqlDbType.NVarChar),                                
				    new SqlParameter("@posion_id", SqlDbType.Int,4),
					new SqlParameter("@report_part_id", SqlDbType.Int,4),
					new SqlParameter("@region_id", SqlDbType.Int,4),
					new SqlParameter("@dimension_id", SqlDbType.Int,4)};
           
            parameters[0].Value = model.Comment_nm;
            parameters[1].Value = model.Posion_id;
            parameters[2].Value = model.Report_part_id;
            parameters[3].Value = model.Region_id;
            parameters[4].Value = model.Dimension_id;

            DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
        }

        public static int DelAllComment(int posID)
        {
            StringBuilder strSql = new StringBuilder();

            strSql.Append("delete ");
            strSql.Append("FROM dbo.tb_Comment ");
            strSql.Append(@"where posion_id = ");
            strSql.Append(posID);

            return DbHelperSQL.ExecuteSql(strSql.ToString());
        }
    }
}
